﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace TB.DALSqlProvider
{
public class DesingerSqlDALProvider:IDAL.IDALDesinger
    { 
        public int Insert(TB.Entity.Desinger obj)
        {
           //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Desinger_insert") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@nickname",obj.nickname==String.Empty?DBNull.Value:(object)obj.nickname);
            cmd.Parameters.AddWithValue("@rzDate",obj.rzDate==String.Empty?DBNull.Value:(object)obj.rzDate);
            cmd.Parameters.AddWithValue("@nickname_idnum",obj.nickname_idnum==String.Empty?DBNull.Value:(object)obj.nickname_idnum);
            cmd.Parameters.AddWithValue("@jianjie",obj.jianjie==String.Empty?DBNull.Value:(object)obj.jianjie);
            cmd.Parameters.AddWithValue("@cjDate",obj.cjDate==String.Empty?DBNull.Value:(object)obj.cjDate);
            cmd.Parameters.AddWithValue("@templateNum",obj.templateNum);
            cmd.Parameters.AddWithValue("@resever1",obj.resever1==String.Empty?DBNull.Value:(object)obj.resever1);
            cmd.Parameters.AddWithValue("@resever2",obj.resever2==String.Empty?DBNull.Value:(object)obj.resever2);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
           return db.ExecuteNonQuery(cmd);
        }
        
        public int Update(TB.Entity.Desinger obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Desinger_update") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@nickname",obj.nickname==String.Empty?DBNull.Value:(object)obj.nickname);
            cmd.Parameters.AddWithValue("@rzDate",obj.rzDate==String.Empty?DBNull.Value:(object)obj.rzDate);
            cmd.Parameters.AddWithValue("@nickname_idnum",obj.nickname_idnum==String.Empty?DBNull.Value:(object)obj.nickname_idnum);
            cmd.Parameters.AddWithValue("@jianjie",obj.jianjie==String.Empty?DBNull.Value:(object)obj.jianjie);
            cmd.Parameters.AddWithValue("@cjDate",obj.cjDate==String.Empty?DBNull.Value:(object)obj.cjDate);
            cmd.Parameters.AddWithValue("@templateNum",obj.templateNum==0?DBNull.Value:(object)obj.templateNum);
            cmd.Parameters.AddWithValue("@resever1",obj.resever1==String.Empty?DBNull.Value:(object)obj.resever1);
            cmd.Parameters.AddWithValue("@resever2",obj.resever2==String.Empty?DBNull.Value:(object)obj.resever2);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public int Delete(TB.Entity.Desinger obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Desinger_delete") as SqlCommand;
            //3.cmd参数赋值
           cmd.Parameters.AddWithValue("@nickname_idnum", obj.nickname_idnum);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public List<TB.Entity.Desinger> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Desinger_loadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader =  db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.Desinger> list = new List<TB.Entity.Desinger>();
            while (reader.Read())
            {
               list.Add(new TB.Entity.Desinger()
               {
                    nickname=reader["nickname"]==DBNull.Value?String.Empty:(string)reader["nickname"],
                    rzDate=reader["rzDate"]==DBNull.Value?String.Empty:(string)reader["rzDate"],
                    nickname_idnum=reader["nickname_idnum"]==DBNull.Value?String.Empty:(string)reader["nickname_idnum"],
                    jianjie=reader["jianjie"]==DBNull.Value?String.Empty:(string)reader["jianjie"],
                    cjDate=reader["cjDate"]==DBNull.Value?String.Empty:(string)reader["cjDate"],
                    templateNum=reader["templateNum"]==DBNull.Value?0:(int)reader["templateNum"],
                    resever1=reader["resever1"]==DBNull.Value?String.Empty:(string)reader["resever1"],
                    resever2=reader["resever2"]==DBNull.Value?String.Empty:(string)reader["resever2"],
               });
           }
           return list;
        }
        
        

        public TB.Entity.Desinger LoadById(string nickname_idnum)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Desinger_loadById") as SqlCommand;
            //3.通过db执行一个DBReader
            cmd.Parameters.AddWithValue("@nickname_idnum", nickname_idnum);
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成一个新的Entity对象 
            TB.Entity.Desinger entity = null;
            while (reader.Read())
            {
                entity = new TB.Entity.Desinger()
                {
                    nickname=reader["nickname"]==DBNull.Value?String.Empty:(string)reader["nickname"],
                    rzDate=reader["rzDate"]==DBNull.Value?String.Empty:(string)reader["rzDate"],
                    nickname_idnum=reader["nickname_idnum"]==DBNull.Value?String.Empty:(string)reader["nickname_idnum"],
                    jianjie=reader["jianjie"]==DBNull.Value?String.Empty:(string)reader["jianjie"],
                    cjDate=reader["cjDate"]==DBNull.Value?String.Empty:(string)reader["cjDate"],
                    templateNum=reader["templateNum"]==DBNull.Value?0:(int)reader["templateNum"],
                    resever1=reader["resever1"]==DBNull.Value?String.Empty:(string)reader["resever1"],
                    resever2=reader["resever2"]==DBNull.Value?String.Empty:(string)reader["resever2"],
                };
            }
            return entity;
        }   
    }
}
